﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using QuanLyShop.DTO;
using DataAcessLayer;
using System.Data.Common;

namespace QuanLyShop.DAO
{
   public class SanPhamDAO
   {
       #region 1. Load danh sách sản phẩm
       public  DataTable LayDanhSachSanPham()
       {
           DataTable dt = null;
           try
           {
               DataProvider dp = new DataProvider();
               string sql = "select * from SAN_PHAM sp where sp.TINHTRANG = 0";
               DbCommand command = dp.CreateCommand();
               command.CommandType = CommandType.Text;
               command.CommandText = sql;
               dt = dp.ExecuteSelectCommand(command);
           }
           catch (Exception e)
           {

               throw new Exception("Lỗi rồi" + e.ToString());
           }
           return dt;
          
       }
       #endregion

       #region 2. Thêm Sản Phẩm

       public  int ThemSanPham(SanPhamDTO sp)
       {
           int kq = 0;
           try
           {
               
               DataProvider dp = new DataProvider();
               String sql = "insert into SAN_PHAM(ID_SANPHAM,ID_MATHANG,ID_GIABAN,ID_TINHTRANG,TENSANPHAM,SERIAL, NGAYNHAP, THONGTINTHEM, TINHTRANG) values ('"+sp.ID_SanPham+"','"+sp.ID_MatHang+"','"+sp.ID_GiaBan+"','"+sp.ID_TinhTrang+"',N'"+sp.TenSanPham+"','"+sp.Serial+"','"+sp.NgayNhap+"',N'"+sp.ThongTinThem+"',"+sp.TinhTrangXoa+")";
               DbCommand command = dp.CreateCommand();
               command.CommandType = CommandType.Text;
               command.CommandText = sql;
               dp.ExecuteNonQueryCommand(command);
               kq = 1;
           }
           catch (Exception ex)
           {
               throw new Exception(ex.Message);
           }
           return kq;
           
       }
       #endregion


       #region 3. Cập nhật Sản Phẩm

       public  int CapNhatSanPham(SanPhamDTO sp)
       {
           int kq = 0;
           try
           {

               DataProvider dp = new DataProvider();
               String sql = "update SAN_PHAM set ID_MATHANG='"+sp.ID_MatHang+"',ID_GIABAN='"+sp.ID_GiaBan+"',ID_TINHTRANG=N'"+sp.ID_TinhTrang+"',TENSANPHAM=N'"+sp.TenSanPham+"',SERIAL='"+sp.Serial+"', NGAYNHAP='"+sp.NgayNhap+"', THONGTINTHEM=N'"+sp.ThongTinThem+"', TINHTRANG="+sp.TinhTrangXoa+" where ID_SANPHAM="+sp.ID_SanPham;
               DbCommand command = dp.CreateCommand();
               command.CommandType = CommandType.Text;
               command.CommandText = sql;
               dp.ExecuteNonQueryCommand(command);
               kq = 1;
           }
           catch (Exception ex)
           {
               throw new Exception(ex.Message);
           }
           return kq;
           
       }
       #endregion

       #region 4. Mã tăng tự động

       public DataTable Lay()
       {

           DataTable dt = null;
           try
           {
               DataProvider dp = new DataProvider();
               string sql = "select ID_SANPHAM from SAN_PHAM";
               DbCommand command = dp.CreateCommand();
               command.CommandType = CommandType.Text;
               command.CommandText = sql;
               dt = dp.ExecuteSelectCommand(command);

           }
           catch (Exception e)
           {

               throw new Exception("Lỗi rồi" + e.ToString());
           }
           return dt;
       }
       public string MaTangTuDong()
       {
           string str = "";
           PhatSinhMaTangTuDong l = new PhatSinhMaTangTuDong();
           DataTable dt = Lay();
           string c = l.LayChuoiNgayThangNam();
           if (dt.Rows.Count == 0)
           {
               str = c + "0001";
           }
           else
           {
               string a = dt.Rows[dt.Rows.Count - 1].ItemArray[0].ToString();
               string b = a.Substring(0, 6);
               if (b.Equals(c))
               {
                   string p = a.Substring(6);
                   int so = int.Parse(p);
                   so = so + 1;
                   if (so < 10)
                   {
                       str = "000" + so;

                   }
                   else if (so < 100)
                   {
                       str = "00" + so;

                   }
                   else if (so < 1000)
                   {
                       str = "0" + so;
                   }
                   str = c + str;
               }
               else
               {
                   str = c + "0001";
               }
           }

           return str;
       }
       #endregion
   }
}
